create proc [dbo].[p_AverageDailyConnectionCount](
	@DateBeginning datetime, 
	@DatabaseID bigint) AS  

set nocount on

declare @week table(
		dtWeekDate datetime,
		iTotalSessions int default 0,
		iTotalConnections int default 0,
		iDBConnections int default 0)    
 
declare @iCurDay int,  		
	@dtDayBeginning datetime,
	@dtDayEnding datetime,
	@iTotalSessions int,   
	@iTotalConnections int,    
	@iDBConnections int    

set @iCurDay=0  
while @iCurDay<7
begin  	
	set @dtDayBeginning=dateadd(day,@iCurDay,@DateBeginning)  	
	set @dtDayEnding=dateadd(minute,-1,dateadd(day,1,@dtDayBeginning))

	insert into @week(
		dtWeekDate,
		iTotalSessions,
		iTotalConnections,
		iDBConnections)
	select
		@dtDayBeginning,
		sum(sessioncount),
		sum(serverconnections),
		sum(databaseconnections)
	from
		connectsummary a
	where
		a.databaseid=@databaseid
		and dbo.MakeDate(a.[Year],a.[Month],a.[Day],a.[Hour],0,0) between @dtDayBeginning and @dtDayEnding

	set @iCurDay=@iCurDay+1  
end   

select   	
	a.dtWeekDate,
	isnull(convert(decimal(18,6),a.iTotalConnections)/convert(decimal(18,6),a.iTotalSessions),0) 'AvgCountOfAllConnections',
	isnull(convert(decimal(18,6),a.iDBConnections)/convert(decimal(18,6),a.iTotalSessions),0) 'AvgCountOfDBConnections'
from   	
	@week a  	  
order by   	
	a.dtWeekDate 
   






